Skip Headers

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 7.2 Oracle Flashback Query: Recovering at the Row Level

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee `'JOHN'` had been deleted from your `EMP` table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the `AS OF` clause of the `SELECT` statement. For example, the following query retrieves the state of the employee record for '`JOHN`' at 9:30AM, April 4, 2005:

    SELECT * FROM EMP AS OF TIMESTAMP
       TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
       WHERE name = 'JOHN';

Restoring John's information to the table `EMP` requires the following update:

    INSERT INTO EMP
        (SELECT * FROM EMP AS OF TIMESTAMP
         TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
         WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

See Also: Oracle Database Application Developer's Guide - Fundamentals for a more extensive discussion of the use of the `SELECT`... `AS OF` SQL statement and extensive examples of its use. Oracle Database SQL Reference for more details on the syntax of the `SELECT`... `AS` `OF` form of the `SELECT` statement.